We will write code to get you the data you need - then automate reporting tasks like summaries, plots, lists and make them downloadable for you clients
#establish database connection
con <-
dbConnect(
RMySQL::MySQL(),
host = "mysql.query.s44",
username = "elessert",
password = .elessert_pass,
port = 3306,
dbname = "s44_prod_eden"
)
#get the data from SQL
LoginData <-
dbGetQuery(
con,
"select c.name, u.company_id, u.job_title, u.first_name, u.last_name, u.total_logins, u.last_login
from user u
join company c
on u.company_id = c.id
where u.first_name not like '%s44%'
and u.first_name not like '%test%'
and u.last_name not like '%s44%'
and u.first_name not like '%task%'
and u.email_address not like '%@sourceintelligence%'
and u.email_address not like '%@s%44%'
and u.email_address like '%@%'
and u.company_id != 1
and u.total_logins not like '%null%'
order by total_logins asc;"
)
LoginData %>%
group_by(name) %>%
mutate(logins = sum(total_logins)) %>%
arrange(desc(logins)) %>%
select(name, logins) %>%
unique %>%
head(15) %>% pander
| name | logins |
|---|---|
| Coca-Cola | 6907 |
| The Home Depot, Inc. | 3355 |
| VF Corporation | 1434 |
| Schneider Electric | 1417 |
| Macy’s, Inc. | 1183 |
| Lab 126 | 1072 |
| Cabela’s Inc. | 962 |
| SCHLUMBERGER | 869 |
| Perry Ellis International | 856 |
| Multi-Fineline Electronix Inc | 813 |
| Nordstrom, Inc. | 762 |
| GUESS?, Inc. | 721 |
| Johnson & Johnson Family of Companies | 678 |
| MSC Industrial Direct Co., Inc. | 652 |
| ANN INC. | 608 |
cons <- dbListConnections(MySQL());lapply(cons, dbDisconnect);rm(cons)
## [[1]]
## [1] TRUE
here is the same example without code
## [[1]]
## [1] TRUE
Here is where we can really shine - customers can get plots that they could never make in Excel, for example:
Note that we can also create presentation ready graphs for the customer contacts that need them for their internal reports
CLICK HERE to suggest an edit to this page!
On the internet people love to tell you when you are wrong!
moar-cats